package com.situ.achievementsystem.department.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.achievementsystem.department.model.DepartmentModel;
import com.situ.achievementsystem.util.JDBCUtil;

public class DepartmentDao {

	private Connection con = null;
	private PreparedStatement ps = null;
	
	public String insert(DepartmentModel model) {
		String sql = "insert into department(code,name,del)values(?,?,?)";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getCode());
			ps.setString(2,model.getName());
			ps.setString(3,model.getDel());
			int res = ps.executeUpdate();
//			System.out.println(res);
			return res + "";
//			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
//	public String insert(EmployeeModel model) {
//		String sql = "insert into person(code,name,pass)values(?,?,?)";
//		return null;
//	}
	
	public String delete(DepartmentModel model) {
		String sql = "delete from department where code = ?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getCode());
			int res = ps.executeUpdate();
			System.out.println(res);
			return res+"";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
	
	public String update(DepartmentModel model) {
		String sql = "update department set name =?,del=? where code=?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getName());
			ps.setString(2,model.getDel());
			ps.setString(3,model.getCode());
			int res = ps.executeUpdate();
//			System.out.println(res);
			return res + "";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
	
	public List<DepartmentModel> select(DepartmentModel model){
		StringBuilder sql = new StringBuilder("select code,name,del from department where 1=1 ");
		List<Object> list = appendWhere(sql,model);
		ResultSet rs = null;
		List<DepartmentModel> result = new ArrayList<>();
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql.toString());
			for(int i=0;i<list.size();i++) {
				ps.setObject(i+1,list.get(i));
			}
			rs = ps.executeQuery();
			while(rs.next()) {
				String code = rs.getString("code");
				String name = rs.getString("name");
				String del = rs.getString("del");
				result.add(new DepartmentModel(code,name,del));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		System.out.println(result);
		return result;
	}

	private List<Object> appendWhere(StringBuilder sql, DepartmentModel model) {
		List<Object> list = new ArrayList<>(); 
		String code = model.getCode();
		if(code!=null && !code.trim().isEmpty()) {
			sql.append(" and code like ? ");
			list.add(code);
		}
		String name = model.getName();
		if(name!=null&&!name.trim().isEmpty()) {
			sql.append(" and name like ? ");
			list.add(name);
		}
		String del = model.getDel();
		if(del!=null && !del.trim().isEmpty()) {
			sql.append(" and del like ? ");
			list.add(del);
		}
		return list;
	}
}
